create or replace package SET3070MARKINGCONTENT is

  -- Author  : 6180
  -- Created : 2009-5-15 13:38:10
  -- Purpose : 

  Function Version Return Varchar2;

  --=====================
  -- Local parameter variables
  --=====================
  Vmadeinchina Varchar2(40) := 'MADE IN CHINA';
  Vchina       Varchar2(40) := 'CHINA';
  Vmaskoption  Product.Maskoption%Type;
  Vdatecode    a_Lotattributes.Datecode%Type;

  Cursor Curproductinfo(In_Containername Container.Containername%Type) Is
    Select c.Maskoption, c.technology
      From Container a, Product c
     Where a.Productid = c.Productid
       And a.Containername = In_Containername;

  Cursor Curdatecodeinfo(In_Containername Container.Containername%Type) Is
    Select b.Datecode
      From Container a, a_Lotattributes b
     Where a.Containerid = b.Containerid
       And a.Containername = In_Containername;

  Rproductinfo  Curproductinfo%Rowtype;
  Rdatecodeinfo Curdatecodeinfo%Rowtype;

  --===============================================================
  -- Function and Procedure declarations
  --===============================================================

  Function Executedata(Containername In Varchar2) Return Varchar2;

  Procedure Executedata2(Containername       In Varchar2,
                         Product             In Varchar2,
                         Packagecategoryname In Varchar2,
                         Materialtypename    In Varchar2,
                         FirstName           In Varchar2,
                         Markingcontent      Out Varchar2);

  Procedure Setsdtopmark1(Containername  In Varchar2,
                          Product        In Varchar2,
                          FirstName      In Varchar2,
                          Markingcontent Out Varchar2);

  Procedure Setmspdtopmark1(Containername  In Varchar2,
                            Product        In Varchar2,
                            FirstName      In Varchar2,
                            Markingcontent Out Varchar2);

  Procedure Setsdbottommark1(Containername    In Varchar2,
                             Materialtypename In Varchar2,
                             Markingcontent   Out Varchar2);

  Procedure Setmicrosdtopmark1(Containername  In Varchar2,
                               Markingcontent Out Varchar2);

  Procedure Setmegasimtopmark1(Containername  In Varchar2,
                               Markingcontent Out Varchar2);

  Procedure Setubgatopmark1(Containername  In Varchar2,
                            Markingcontent Out Varchar2);

  Procedure Setinandtopmark1(Containername  In Varchar2,
                             FirstName      In Varchar2,
                             Markingcontent Out Varchar2);

--===========================================================================
-- End of package specification
--===========================================================================
End;
/
create or replace package body SET3070MARKINGCONTENT is

  --=====================
  -- Package information
  --=====================
  Function Version Return Varchar2 Is
  Begin
    Return('1.0.1000');
  End;

  --=====================
  -- ExecuteData
  --=====================
  Function Executedata(Containername In Varchar2) Return Varchar2 Is
    --=====================
    -- Input parameter variables
    --=====================
  
    --=====================
    -- Cursor Declarations
    --=====================
    Cursor Curcontainerinfo(In_Containername Container.Containername%Type) Is
      Select B1.Productname,
             Pc.Packagecategoryname,
             c.Materialtypename,
             a.firstname
        From Container         a,
             Product           b,
             Productbase       B1,
             a_Materialtype    c,
             a_Packagecategory Pc
       Where a.Productid = b.Productid
         And b.Materialtypeid = c.Materialtypeid
         And b.Packagecategoryid = Pc.Packagecategoryid
         And a.Containername = In_Containername;
  
    --=====================
    -- Local variables
    --=====================
    Rcontainerinfo       Curcontainerinfo%Rowtype;
    Vmarkingcontent      Varchar2(40);
    Vproduct             Varchar2(40);
    Vpackagecategoryname Varchar2(40);
    Vmaterialtypename    Varchar2(40);
    VFirstName           Varchar2(40);
    --=====================
    -- Pragma Declarations
    --=====================
    Pragma Autonomous_Transaction;
    Eexit Exception;
    Pragma Exception_Init(Eexit, -20000);
    Eapperror Exception;
    Pragma Exception_Init(Eapperror, -20005);
  
  Begin
    --=====================
    -- Fetch packagecategoryname and materialtypename info
    --=====================
    Open Curcontainerinfo(Containername);
    Fetch Curcontainerinfo
      Into Rcontainerinfo;
    If Curcontainerinfo%Found Then
      Vproduct             := Rcontainerinfo.Productname;
      Vpackagecategoryname := Rcontainerinfo.Packagecategoryname;
      Vmaterialtypename    := Rcontainerinfo.Materialtypename;
      VFirstName           := Rcontainerinfo.Firstname;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'ProductName' ||
                              ' Packagecategoryname' || ' Materialtypename');
    End If;
    Close Curcontainerinfo;
  
    --=====================
    -- Call the procedure
    --=====================
    Executedata2(Containername,
                 Vproduct,
                 Vpackagecategoryname,
                 Vmaterialtypename,
                 VFirstName,
                 Vmarkingcontent);
    --=====================
    -- Return success
    --=====================
    Commit;
    Return(Vmarkingcontent);
  
  Exception
    When Eexit Then
      Rollback;
      Return('SetMarkingContent.ExecuteData->Exited prematurely');
    When Eapperror Then
      Rollback;
      Return(Replace(Sqlerrm, 'ORA-20005: '));
    When Others Then
      Rollback;
      Return('SetMarkingContent.ExecuteData->' || Sqlerrm);
  End Executedata;

  --=====================
  -- ExecuteData2
  --=====================
  Procedure Executedata2(Containername       In Varchar2,
                         Product             In Varchar2,
                         Packagecategoryname In Varchar2,
                         Materialtypename    In Varchar2,
                         FirstName           In Varchar2,
                         Markingcontent      Out Varchar2) Is
  
    --=====================
    -- Local variables
    --=====================
  
    --=====================
    -- Pragma Declarations
    --=====================
    Eexit Exception;
    Pragma Exception_Init(Eexit, -20000);
    Eapperror Exception;
    Pragma Exception_Init(Eapperror, -20005);
  
  Begin
    --=====================
    -- Check Mark Id
    --=====================
    Markingcontent := 'SKIP';
  
    If (Packagecategoryname = 'UBGA' or Packagecategoryname = 'ULGA') And
       Substr(Materialtypename, 1, 9) = 'MEMORYDIE' Then
      Setubgatopmark1(Containername ,Markingcontent);
    End If;
  
    If (Packagecategoryname = 'SD-SIP' OR Packagecategoryname = 'MS-PROHG') And
       Substr(Materialtypename, 1, 9) = 'MEMORYDIE' Then
      Setsdtopmark1(Containername, Product, FirstName, Markingcontent);
    End If;
  
    /*If Packagecategoryname = 'MS-PROHG' And Substr(Materialtypename, 1, 9) = 'MEMORYDIE' Then
      Setmspdtopmark1(Containername, Product, Markingcontent);
    End If;*/
  
    If (Packagecategoryname = 'SD-SIP' Or Packagecategoryname = 'MS-PROHG') And
       (Substr(Materialtypename, 1, 12) = 'PHANTOM-CARD' Or
       Substr(Materialtypename, 1, 4) = 'CARD') Then
      Setsdbottommark1(Containername,
                       Materialtypename,
                       Markingcontent);
    End If;
  
    If (Packagecategoryname = 'MICRO-SD' Or Packagecategoryname = 'M2') And
       Substr(Materialtypename, 1, 9) = 'MEMORYDIE' Then
      Setmicrosdtopmark1(Containername, Markingcontent);
    End If;
  
    If Packagecategoryname = 'MEGASIM' And
       Substr(Materialtypename, 1, 9) = 'MEMORYDIE' Then
      Setmegasimtopmark1(Containername, Markingcontent);
    End If;
  
    If (Packagecategoryname = 'INAND2' Or Packagecategoryname = 'INAND3' Or
       Packagecategoryname = 'INAND4') And
       Substr(Materialtypename, 1, 9) = 'MEMORYDIE' Then
      Setinandtopmark1(Containername, FirstName, Markingcontent);
    End If;
  
  Exception
    When Eexit Then
      Rollback;
    When Eapperror Then
      Rollback;
      Raise_Application_Error(-20005, Sqlerrm);
    When Others Then
      Rollback;
      Raise_Application_Error(-20005,
                              'SetDateCode.ExecuteData2->' || Sqlerrm);
  End Executedata2;

  Procedure Setsdtopmark1(Containername  In Varchar2,
                          Product        In Varchar2,
                          FirstName      In Varchar2,
                          Markingcontent Out Varchar2) Is
    --=====================
    -- Input parameter variables
    --=====================
  
    --=====================
    -- Cursor Declarations
    --=====================
    --=====================
    -- No need get wafer runnumber info
    -- Deng Chao, 2009-03-17
    --=====================
    /*Cursor Curwaferrunnumberinfo(In_Containername Container.Containername%Type) Is
      Select Distinct t.Waferrunnumber
        From Container C1, a_Lotwaferrunnumbers t
       Where C1.Containerid In
             (Select Distinct c.Containerid
                From Container a, Mfgorder b, a_Lotattributes c
               Where a.Mfgorderid = b.Mfgorderid
                 And c.Workorder In (b.Mfgordername, b.Workordernumber)
                 And a.Containername = In_Containername)
         And C1.Originalcontainerid = t.Containerid
         And t.Waferrunnumbercategory In ('M', 'T');
    
    Cursor Curworkorderinfo(In_Containername Container.Containername%Type) Is
      Select b.Workordernumber
        From Container a, Mfgorder b
       Where a.Mfgorderid = b.Mfgorderid
         And a.Containername = In_Containername;*/
  
    Cursor Curqadpninfo(In_Containername Container.Containername%Type) Is
      Select Substr(b.Qaddevice, 7) Qadpn
        From Container a, Product b
       Where a.Productid = b.Productid
         And a.Containername = In_Containername;
  
    --=====================
    -- Local variables
    --=====================
    --Rwaferrunnumberinfo Curwaferrunnumberinfo%Rowtype;
    --Rworkorderinfo      Curworkorderinfo%Rowtype;
    Rqadpninfo Curqadpninfo%Rowtype;
    --Vwaferrun_Wo        Varchar2(100);
    Vqadpn Varchar2(40);
  
    --=====================
    -- Pragma Declarations
    --=====================
    Eexit Exception;
    Pragma Exception_Init(Eexit, -20000);
    Eapperror Exception;
    Pragma Exception_Init(Eapperror, -20005);
  
  Begin
    --=====================
    -- Get DateCode
    --=====================
    Open Curdatecodeinfo(Containername);
    Fetch Curdatecodeinfo
      Into Rdatecodeinfo;
    If Curdatecodeinfo%Found Then
      Vdatecode := Rdatecodeinfo.Datecode;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Container' ||
                              ' DateCode is null');
    End If;
    Close Curdatecodeinfo;
  
    --=====================
    -- Get QADPN
    --=====================
    Open Curqadpninfo(Containername);
    Fetch Curqadpninfo
      Into Rqadpninfo;
    If Curqadpninfo%Found Then
      Vqadpn := Rqadpninfo.Qadpn;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Container' ||
                              ' QAD_Device is null');
    End If;
    Close Curqadpninfo;
  
    --=====================
    -- Get WaferRunNumber or Work Order
    --=====================
    --Vwaferrun_Wo := Null;
  
    --Get all records then check record count
    --=====================
    -- No need wafer runnumber info
    -- Deng Chao, 2009-03-17
    --=====================
    /*Open Curwaferrunnumberinfo(Containername);
    Loop
      Fetch Curwaferrunnumberinfo
        Into Rwaferrunnumberinfo;
      Exit When Curwaferrunnumberinfo%Notfound;
    End Loop;
    
    If Curwaferrunnumberinfo%Rowcount = 1 Then
      Vwaferrun_Wo := UPPER(Rwaferrunnumberinfo.Waferrunnumber);
    Else
      Open Curworkorderinfo(Containername);
      Fetch Curworkorderinfo
        Into Rworkorderinfo;
      If Curworkorderinfo%Found Then
        Vwaferrun_Wo := UPPER(Rworkorderinfo.Workordernumber);
      End If;
      Close Curworkorderinfo;
    End If;
    Close Curwaferrunnumberinfo;*/
    --=====================
    -- Set the Marking Content
    --=====================
    Delete From A_3070MARKCONTENT t Where t.Lotnumber = Containername;
  
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 1, Vdatecode, Null);
  
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 2, Vqadpn, Null);
  
    --=====================
    -- No need wafer runnumber info
    -- Deng Chao, 2009-03-17
    --=====================
    /*Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 3, Vwaferrun_Wo, Null);*/
  
    --=====================
    -- Raise line
    -- Deng Chao, 2009-03-17
    --=====================
    /*Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 4, Containername, Null);
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 5, Vchina, Null);*/
  
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 3, FirstName, Null);
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 4, Vchina, Null);
  
    Markingcontent := 'PASS';
  
  Exception
    When Eexit Then
      Rollback;
    When Eapperror Then
      Rollback;
      Raise_Application_Error(-20005, Sqlerrm);
    When Others Then
      Rollback;
      Raise_Application_Error(-20005,
                              'SetDateCode.SetSDTOPMARK->' || Sqlerrm);
  End Setsdtopmark1;

  Procedure Setsdbottommark1(Containername    In Varchar2,
                             Materialtypename In Varchar2,
                             Markingcontent   Out Varchar2) Is
    --=====================
    -- Input parameter variables
    --=====================
  
    --=====================
    -- Cursor Declarations
    --=====================
    Cursor Curphantomdesc(In_Containername Container.Containername%Type) Is
      Select c.Description
        From Container a, Mfgorder b, Product c
       Where b.Mfgorderid = a.Mfgorderid
         And c.Productid = b.Phantomproductid
         And a.Containername = In_Containername;
  
    Cursor Curproductdesc(In_Containername Container.Containername%Type) Is
      Select c.Description
        From Container a, Product c
       Where a.Productid = c.Productid
         And a.Containername = In_Containername;
  
    --=====================
    -- Local variables
    --=====================
    Rphantomdesc Curphantomdesc%Rowtype;
    Rproductdesc Curproductdesc%Rowtype;
    Vdescription Product.Description%Type;
  
    --=====================
    -- Pragma Declarations
    --=====================
    Eexit Exception;
    Pragma Exception_Init(Eexit, -20000);
    Eapperror Exception;
    Pragma Exception_Init(Eapperror, -20005);
  
  Begin
    --=====================
    -- Get Description
    --=====================
    Vdescription := Null;
  
    If Substr(Materialtypename, 1, 12) = 'PHANTOM-CARD' Then
      Open Curproductdesc(Containername);
      Fetch Curproductdesc
        Into Rproductdesc;
      If Curproductdesc%Found Then
        Vdescription := Rproductdesc.Description;
      Else
        Raise_Application_Error(-20005,
                                'SetMarkingContent_E1003 ' ||
                                'Product not found');
      End If;
      Close Curproductdesc;
    Else
      Open Curphantomdesc(Containername);
      Fetch Curphantomdesc
        Into Rphantomdesc;
      If Curphantomdesc%Found Then
        Vdescription := Rphantomdesc.Description;
      Else
        Raise_Application_Error(-20005,
                                'SetMarkingContent_E1003 ' ||
                                'Phantom not found');
      End If;
      Close Curphantomdesc;
    End If;
  
    --=====================
    -- Get DateCode
    --=====================
    Open Curdatecodeinfo(Containername);
    Fetch Curdatecodeinfo
      Into Rdatecodeinfo;
    If Curdatecodeinfo%Found Then
      Vdatecode := Rdatecodeinfo.Datecode;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Container' ||
                              ' DateCode is null');
    End If;
    Close Curdatecodeinfo;
    --=====================
    -- Set the Marking Content
    --=====================
    Delete From A_3070MARKCONTENT t Where t.Lotnumber = Containername;
  
    If Vdescription Is Not Null Then
      Insert Into A_3070MARKCONTENT
        (Lotnumber, Line, Linevalue1, Linevalue2)
      Values
        (Containername, 1, Vdescription, Null);
      Insert Into A_3070MARKCONTENT
        (Lotnumber, Line, Linevalue1, Linevalue2)
      Values
        (Containername, 2, Vdatecode, Null);
      Insert Into A_3070MARKCONTENT
        (Lotnumber, Line, Linevalue1, Linevalue2)
      Values
        (Containername, 3, Vmadeinchina, Null);
    Else
      Insert Into A_3070MARKCONTENT
        (Lotnumber, Line, Linevalue1, Linevalue2)
      Values
        (Containername, 1, Vdatecode, Null);
      Insert Into A_3070MARKCONTENT
        (Lotnumber, Line, Linevalue1, Linevalue2)
      Values
        (Containername, 2, Vmadeinchina, Null);
    End If;
  
    Markingcontent := 'PASS';
  
  Exception
    When Eexit Then
      Rollback;
    When Eapperror Then
      Rollback;
      Raise_Application_Error(-20005, Sqlerrm);
    When Others Then
      Rollback;
      Raise_Application_Error(-20005,
                              'SetDateCode.SetSDButtomMARK1->' || Sqlerrm);
  End Setsdbottommark1;

  Procedure Setmicrosdtopmark1(Containername  In Varchar2, 
                               Markingcontent Out Varchar2) Is
    --=====================
    -- Input parameter variables
    --=====================
  
    --=====================
    -- Cursor Declarations
    --=====================
    Cursor Curmarkseqinfo(In_Containername Container.Containername%Type) Is
      Select b.Marksequence
        From Container a, a_Lotattributes b
       Where a.Containerid = b.Containerid
         And a.Containername = In_Containername;
    --=====================
    -- Local variables
    --=====================
    Rmarkseqinfo  Curmarkseqinfo%Rowtype;
    Vmarksequence a_Lotattributes.Marksequence%Type;
  
    --=====================
    -- Pragma Declarations
    --=====================
    Eexit Exception;
    Pragma Exception_Init(Eexit, -20000);
    Eapperror Exception;
    Pragma Exception_Init(Eapperror, -20005);
  
  Begin
  
    --=====================
    -- Get DateCode
    --=====================
    Open Curdatecodeinfo(Containername);
    Fetch Curdatecodeinfo
      Into Rdatecodeinfo;
    If Curdatecodeinfo%Found Then
      Vdatecode := Rdatecodeinfo.Datecode;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Container' ||
                              ' DateCode is null');
    End If;
    Close Curdatecodeinfo;
  
    --=====================
    -- Get MarkSeq
    --=====================
    Open Curmarkseqinfo(Containername);
    Fetch Curmarkseqinfo
      Into Rmarkseqinfo;
    If Curmarkseqinfo%Found Then
      Vmarksequence := Rmarkseqinfo.Marksequence;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Container' ||
                              ' Mark Sequence is null');
    End If;
  
    Close Curmarkseqinfo;
    --=====================
    -- Set the Marking Content
    --=====================
    Delete From A_3070MARKCONTENT t Where t.Lotnumber = Containername;
  
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 1, Vdatecode, Vmarksequence);
  
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 2, Vmadeinchina, Null);
  
    Markingcontent := 'PASS';
  
  Exception
    When Eexit Then
      Rollback;
    When Eapperror Then
      Rollback;
      Raise_Application_Error(-20005, Sqlerrm);
    When Others Then
      Rollback;
      Raise_Application_Error(-20005,
                              'SetDateCode.SetMicroSDTOPMARK->' || Sqlerrm);
  End Setmicrosdtopmark1;

  Procedure Setmegasimtopmark1(Containername  In Varchar2,
                               Markingcontent Out Varchar2) Is
    --=====================
    -- Input parameter variables
    --=====================
  
    --=====================
    -- Cursor Declarations
    --=====================
    Cursor Curmarkseqinfo(In_Containername Container.Containername%Type) Is
      Select b.Marksequence
        From Container a, a_Lotattributes b
       Where a.Containerid = b.Containerid
         And a.Containername = In_Containername;
    --=====================
    -- Local variables
    --=====================
    Rmarkseqinfo  Curmarkseqinfo%Rowtype;
    Vmarksequence a_Lotattributes.Marksequence%Type;
  
    --=====================
    -- Pragma Declarations
    --=====================
    Eexit Exception;
    Pragma Exception_Init(Eexit, -20000);
    Eapperror Exception;
    Pragma Exception_Init(Eapperror, -20005);
  
  Begin
  
    --=====================
    -- Get DateCode
    --=====================
    Open Curdatecodeinfo(Containername);
    Fetch Curdatecodeinfo
      Into Rdatecodeinfo;
    If Curdatecodeinfo%Found Then
      Vdatecode := Rdatecodeinfo.Datecode;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Container' ||
                              ' DateCode is null');
    End If;
    Close Curdatecodeinfo;
  
    --=====================
    -- Get MarkSeq
    --=====================
    Open Curmarkseqinfo(Containername);
    Fetch Curmarkseqinfo
      Into Rmarkseqinfo;
    If Curmarkseqinfo%Found Then
      Vmarksequence := Rmarkseqinfo.Marksequence;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Container' ||
                              ' Mark Sequence is null');
    End If;
  
    Close Curmarkseqinfo;
    --=====================
    -- Set the Marking Content
    --=====================
    Delete From A_3070MARKCONTENT t Where t.Lotnumber = Containername;
  
    If Vmarksequence Is Null Then
      Insert Into A_3070MARKCONTENT
        (Lotnumber, Line, Linevalue1, Linevalue2)
      Values
        (Containername, 1, Vdatecode, Vmarksequence);
    Else
      Insert Into A_3070MARKCONTENT
        (Lotnumber, Line, Linevalue1, Linevalue2)
      Values
        (Containername, 1, Vdatecode || '-' || Vmarksequence, Null);
    End If;
  
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 2, Vmadeinchina, Null);
  
    Markingcontent := 'PASS';
  
  Exception
    When Eexit Then
      Rollback;
    When Eapperror Then
      Rollback;
      Raise_Application_Error(-20005, Sqlerrm);
    When Others Then
      Rollback;
      Raise_Application_Error(-20005,
                              'SetDateCode.SetMicroSDTOPMARK->' || Sqlerrm);
  End Setmegasimtopmark1;

  Procedure Setubgatopmark1(Containername  In Varchar2,
                            Markingcontent Out Varchar2) Is
    --=====================
    -- Input parameter variables
    --=====================
  
    --=====================
    -- Cursor Declarations
    --=====================
  
    Cursor Curwaferrunnumberinfo(In_Containername Container.Containername%Type) Is
      Select Distinct t.Waferrunnumber
        From Container C1, a_Lotwaferrunnumbers t
       Where C1.Containerid In
             (Select Distinct c.Containerid
                From Container a, Mfgorder b, a_Lotattributes c
               Where a.Mfgorderid = b.Mfgorderid
                 And c.Workorder In (b.Mfgordername, b.Workordernumber)
                 And a.Containername = In_Containername)
         And C1.Originalcontainerid = t.Containerid
         And t.Waferrunnumbercategory In ('M', 'T');
  
    Cursor Curworkorderinfo(In_Containername Container.Containername%Type) Is
      Select b.Workordernumber
        From Container a, Mfgorder b
       Where a.Mfgorderid = b.Mfgorderid
         And a.Containername = In_Containername;
  
    Cursor Curqadpninfo(In_Containername Container.Containername%Type) Is
      Select Substr(b.Qaddevice, 13) Qadpn
        From Container a, Product b
       Where a.Productid = b.Productid
         And a.Containername = In_Containername;
  
    --=====================
    -- Local variables
    --=====================
    Rwaferrunnumberinfo Curwaferrunnumberinfo%Rowtype;
    Rworkorderinfo      Curworkorderinfo%Rowtype;
    Vwaferrun_Wo        Varchar2(100);
    vProductName        Varchar2(10);
  
    --=====================
    -- Pragma Declarations
    --=====================
    Eexit Exception;
    Pragma Exception_Init(Eexit, -20000);
    Eapperror Exception;
    Pragma Exception_Init(Eapperror, -20005);
  
  Begin
    --=====================
    -- Get Maskoption
    --=====================
    Open Curproductinfo(Containername);
    Fetch Curproductinfo
      Into Rproductinfo;
    If Curproductinfo%Found Then
      Vmaskoption := Rproductinfo.technology;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Product' ||
                              ' Maskoption is null');
    End If;
    Close Curproductinfo;
  
    Open Curqadpninfo(Containername);
    Fetch Curqadpninfo
      into vProductName;
    close Curqadpninfo;
  
    IF Vmaskoption = '56nm' and length(Vmaskoption) = 4 THEN
      vProductName := '0' + vProductName;
    END IF;
    --=====================
    -- Get DateCode
    --=====================
    Open Curdatecodeinfo(Containername);
    Fetch Curdatecodeinfo
      Into Rdatecodeinfo;
    If Curdatecodeinfo%Found Then
      vDateCode := Rdatecodeinfo.Datecode || '-' || vProductName;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Container' ||
                              ' DateCode is null');
    End If;
    Close Curdatecodeinfo;
  
    --=====================
    -- Get WaferRunNumber or Work Order
    --=====================
    Vwaferrun_Wo := Null;
  
    --Get all records then check record count
    Open Curwaferrunnumberinfo(Containername);
    Loop
      Fetch Curwaferrunnumberinfo
        Into Rwaferrunnumberinfo;
      Exit When Curwaferrunnumberinfo%Notfound;
    End Loop;
  
    If Curwaferrunnumberinfo%Rowcount = 1 Then
      Vwaferrun_Wo := Rwaferrunnumberinfo.Waferrunnumber;
    Else
      Open Curworkorderinfo(Containername);
      Fetch Curworkorderinfo
        Into Rworkorderinfo;
      If Curworkorderinfo%Found Then
        Vwaferrun_Wo := Rworkorderinfo.Workordernumber;
      End If;
      Close Curworkorderinfo;
    End If;
    Close Curwaferrunnumberinfo;
    --=====================
    -- Set the Marking Content
    --=====================
    Delete From A_3070MARKCONTENT t Where t.Lotnumber = Containername;
  
    /*Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 1, Vmaskoption, Null);*/
  
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 1, vDateCode, Null);
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 2, Vwaferrun_Wo, Null);
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 3, Containername, Null);
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 4, Vchina, Null);
  
    Markingcontent := 'PASS';
  
  Exception
    When Eexit Then
      Rollback;
    When Eapperror Then
      Rollback;
      Raise_Application_Error(-20005, Sqlerrm);
    When Others Then
      Rollback;
      Raise_Application_Error(-20005,
                              'SetDateCode.SetUBGATOPMARK->' || Sqlerrm);
  End Setubgatopmark1;

  Procedure Setinandtopmark1(Containername  In Varchar2,
                             FirstName      In Varchar2,
                             Markingcontent Out Varchar2) Is
    --=====================
    -- Input parameter variables
    --=====================
  
    --=====================
    -- Cursor Declarations
    --=====================
  
    --=====================
    -- Local variables
    --=====================
  
    --=====================
    -- Pragma Declarations
    --=====================
    Eexit Exception;
    Pragma Exception_Init(Eexit, -20000);
    Eapperror Exception;
    Pragma Exception_Init(Eapperror, -20005);
  
  Begin
    --=====================
    -- Get Maskoption
    --=====================
    Open Curproductinfo(Containername);
    Fetch Curproductinfo
      Into Rproductinfo;
    If Curproductinfo%Found Then
      Vmaskoption := Rproductinfo.Maskoption;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Product' ||
                              ' Maskoption is null');
    End If;
    Close Curproductinfo;
  
    --=====================
    -- Get DateCode
    --=====================
    Open Curdatecodeinfo(Containername);
    Fetch Curdatecodeinfo
      Into Rdatecodeinfo;
    If Curdatecodeinfo%Found Then
      Vdatecode := Rdatecodeinfo.Datecode;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Container' ||
                              ' DateCode is null');
    End If;
    Close Curdatecodeinfo;
    --=====================
    -- Set the Marking Content
    --=====================
    Delete From A_3070MARKCONTENT t Where t.Lotnumber = Containername;
  
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 1, Vmaskoption, Null);
  
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 2, Vdatecode, Null);
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 3, FirstName || 'CHINA', Null);
  
    Markingcontent := 'PASS';
  
  Exception
    When Eexit Then
      Rollback;
    When Eapperror Then
      Rollback;
      Raise_Application_Error(-20005, Sqlerrm);
    When Others Then
      Rollback;
      Raise_Application_Error(-20005,
                              'SetDateCode.SetINANDTOPMARK->' || Sqlerrm);
  End Setinandtopmark1;

  Procedure Setmspdtopmark1(Containername  In Varchar2,
                            Product        In Varchar2,
                            FirstName      In Varchar2,
                            Markingcontent Out Varchar2) Is
    --=====================
    -- Input parameter variables
    --=====================
  
    --=====================
    -- Cursor Declarations
    --=====================
    Cursor Curwaferrunnumberinfo(In_Containername Container.Containername%Type) Is
      Select Distinct t.Waferrunnumber
        From Container C1, a_Lotwaferrunnumbers t
       Where C1.Containerid In
             (Select Distinct c.Containerid
                From Container a, Mfgorder b, a_Lotattributes c
               Where a.Mfgorderid = b.Mfgorderid
                 And c.Workorder In (b.Mfgordername, b.Workordernumber)
                 And a.Containername = In_Containername)
         And C1.Originalcontainerid = t.Containerid
         And t.Waferrunnumbercategory In ('M', 'T');
  
    Cursor Curworkorderinfo(In_Containername Container.Containername%Type) Is
      Select b.Workordernumber
        From Container a, Mfgorder b
       Where a.Mfgorderid = b.Mfgorderid
         And a.Containername = In_Containername;
  
    Cursor Curqadpninfo(In_Containername Container.Containername%Type) Is
      Select Substr(b.Qaddevice, 7) Qadpn
        From Container a, Product b
       Where a.Productid = b.Productid
         And a.Containername = In_Containername;
    --=====================
    -- Local variables
    --=====================
    Rwaferrunnumberinfo Curwaferrunnumberinfo%Rowtype;
    Rworkorderinfo      Curworkorderinfo%Rowtype;
    Rqadpninfo          Curqadpninfo%Rowtype;
    Vwaferrun_Wo        Varchar2(100);
    Vqadpn              Varchar2(40);
  
    --=====================
    -- Pragma Declarations
    --=====================
    Eexit Exception;
    Pragma Exception_Init(Eexit, -20000);
    Eapperror Exception;
    Pragma Exception_Init(Eapperror, -20005);
  
  Begin
    --=====================
    -- Get DateCode
    --=====================
    Open Curdatecodeinfo(Containername);
    Fetch Curdatecodeinfo
      Into Rdatecodeinfo;
    If Curdatecodeinfo%Found Then
      Vdatecode := Rdatecodeinfo.Datecode;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Container' ||
                              ' DateCode is null');
    End If;
    Close Curdatecodeinfo;
  
    --=====================
    -- Get QADPN
    --=====================
    Open Curqadpninfo(Containername);
    Fetch Curqadpninfo
      Into Rqadpninfo;
    If Curqadpninfo%Found Then
      Vqadpn := Rqadpninfo.Qadpn;
    Else
      Raise_Application_Error(-20005,
                              'SetMarkingContent_E1003 ' || 'Container' ||
                              ' QAD_Device is null');
    End If;
    Close Curqadpninfo;
  
    --=====================
    -- Get WaferRunNumber or Work Order
    --=====================
    Vwaferrun_Wo := Null;
  
    --Get all records then check record count
    Open Curwaferrunnumberinfo(Containername);
    Loop
      Fetch Curwaferrunnumberinfo
        Into Rwaferrunnumberinfo;
      Exit When Curwaferrunnumberinfo%Notfound;
    End Loop;
  
    If Curwaferrunnumberinfo%Rowcount = 1 Then
      Vwaferrun_Wo := UPPER(Rwaferrunnumberinfo.Waferrunnumber);
    Else
      Open Curworkorderinfo(Containername);
      Fetch Curworkorderinfo
        Into Rworkorderinfo;
      If Curworkorderinfo%Found Then
        Vwaferrun_Wo := UPPER(Rworkorderinfo.Workordernumber);
      End If;
      Close Curworkorderinfo;
    End If;
    Close Curwaferrunnumberinfo;
    --=====================
    -- Set the Marking Content
    --=====================
    Delete From A_3070MARKCONTENT t Where t.Lotnumber = Containername;
  
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 1, Vdatecode, Null);
  
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 2, Vqadpn, Null);
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 3, Vwaferrun_Wo, Null);
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 4, FirstName, Null);
    Insert Into A_3070MARKCONTENT
      (Lotnumber, Line, Linevalue1, Linevalue2)
    Values
      (Containername, 5, Vchina, Null);
  
    Markingcontent := 'PASS';
  
  Exception
    When Eexit Then
      Rollback;
    When Eapperror Then
      Rollback;
      Raise_Application_Error(-20005, Sqlerrm);
    When Others Then
      Rollback;
      Raise_Application_Error(-20005,
                              'SetDateCode.SetSDTOPMARK->' || Sqlerrm);
  End Setmspdtopmark1;
  --===========================================================================
-- End of package body
--===========================================================================
End;
/
